/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.fandi.dao.impl;

import com.fandi.config.DatabaseConnection;
import com.fandi.dao.CategoryDao;
import com.fandi.model.CategoryModel;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Fandy
 */
public class CategoryDaoImpl implements CategoryDao {

    private final Connection conn;
    
    private final String insert = "INSERT INTO tb_category VALUES(null, ?, ?, ?, now())";
    private final String update = "UPDATE tb_category SET code=?, category_name=?, description=?, last_upd=now() WHERE uid=?";
    private final String delete = "DELETE FROM tb_category WHERE code=?";
    private final String select = "SELECT * FROM tb_category";
    private final String selectBy = "SELECT * FROM tb_category WHERE code=?";

    public CategoryDaoImpl() {
        conn = DatabaseConnection.getConnection();
    }
    
    @Override
    public void insert(CategoryModel model) throws SQLException {
        
        try (PreparedStatement ps = conn.prepareStatement(insert)) {
            ps.setString(1, model.getCode());
            ps.setString(2, model.getName());
            ps.setString(3, model.getDescription());
            
            ps.executeUpdate();
        } catch (SQLException se) {
            se.printStackTrace();
        }
    }
    
    @Override
    public void update(CategoryModel model) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(update)) {
            ps.setString(1, model.getCode());
            ps.setString(2, model.getName());
            ps.setString(3, model.getDescription());
            ps.setInt(4, model.getId());

            ps.executeUpdate();
            
            System.out.println(ps.toString());
            
        } catch (SQLException se) {
            se.printStackTrace();
        }
    }

    @Override
    public boolean delete(String code) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(delete)) {
            ps.setString(1, code);

            ps.executeUpdate();
            return true;
        } catch (SQLException se) {
            se.printStackTrace();
            return false;
        }
    }
    
    @Override
    public List<CategoryModel> selectAll() throws SQLException {
        
        List<CategoryModel> list = new ArrayList<>();
        
        try (
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(select)
            ) {
            
            while (rs.next()) {
                CategoryModel model = new CategoryModel();
                model.setId(rs.getInt("uid"));
                model.setCode(rs.getString("code"));
                model.setName(rs.getString("category_name"));
                model.setDescription(rs.getString("description"));
                model.setLastUpd(rs.getDate("last_upd"));

                list.add(model);
            }

            return list;
        } catch (SQLException se) {
            se.printStackTrace();
            return null;
        }
    }
    
    @Override
    public CategoryModel selectBy(String code) throws SQLException {
        CategoryModel model = null;
        
        try (
            PreparedStatement ps = conn.prepareStatement(selectBy);
            ) {
            
            ps.setString(1, code);
            ResultSet rs = ps.executeQuery();
            
            if (rs.next()) {
                model = new CategoryModel();
                model.setId(rs.getInt("uid"));
                model.setCode(rs.getString("code"));
                model.setName(rs.getString("category_name"));
                model.setDescription(rs.getString("description"));
                model.setLastUpd(rs.getDate("last_upd"));
            }

            return model;
        } catch (SQLException se) {
            se.printStackTrace();
            return null;
        }
    }
}
